/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.lundberg.podcasts.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.lundberg.podcasts.types.Channel;
import org.lundberg.podcasts.types.Item;
import org.xml.sax.SAXException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 *
 * @author lundbergg
 */
public class PodcastDB extends SQLiteOpenHelper
{
	//private static final String USER_INFO_URL = "http://www.google.com/reader/api/0/user-info";
    public static final String PREFS_FILE_NAME = "preferences";
    public static final String PREF_CURRENT_ITEM = "current_item_guid";
    public static final String PREF_AUTH_TOKEN = "auth_token";
    public static final String PREF_USER_ID = "user_id";
    private static final int DATABASE_VERSION = 6;
    private static final String DATABASE_NAME = "podcasts.db";
    public static final String ITEMS_TABLE = "items";
    public static final String CHANNELS_TABLE = "channels";
    public static final String[] CHANNELS_COLUMNS = {"_id",
													"title",
													"author",
													"url",
													"imageURL",
													"pubDate",
													"new_items"};
    
    private static final String CREATE_CHANNELS_TABLE = "CREATE TABLE "+CHANNELS_TABLE
														+"(_id INTEGER primary key autoincrement,"
														+"title TEXT,"
											            +"author TEXT,"
											            +"url TEXT,"
											            +"imageURL TEXT,"
											            +"pubDate TEXT,"
											            +"new_items INTEGER)";
    
    private static final String CREATE_ITEMS_TABLE = "CREATE TABLE "+ITEMS_TABLE
											            +"(guid TEXT primary key,"
											            +"channel_id INT,"
											            +"title TEXT,"
											            +"pubDate TEXT,"
											            +"listened INT,"
											            +"position INT,"
											            +"size INT,"
											            +"duration TEXT,"
											            +"enclosureURL TEXT,"
											            +"description TEXT,"
											            +"_id INTEGER)";
    
    public static final String[] ITEMS_COLUMNS =  {"guid",
	                                                 "channel_id",
	                                                 "title",
	                                                 "pubDate",
	                                                 "listened",
	                                                 "position",
	                                                 "size",
	                                                 "duration",
	                                                 "enclosureURL",
	                                                 "description",
	                                                 "_id"};

    @Override
	public void onCreate(SQLiteDatabase db)
    {
    	db.execSQL(CREATE_CHANNELS_TABLE);
    	db.execSQL(CREATE_ITEMS_TABLE);
    }
    
    @Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {		
    	
    	// remove the description column from the channels table
    	// and change the id column to _id
    	if (oldVersion < 5)
    	{
    		ArrayList<Channel> channels = null;
    		Cursor c = null;
    		
    		if (oldVersion < 4) {
    			c = db.query(false, CHANNELS_TABLE, new String[] {"id",
																"title",
																"author",
																"url",
																"imageURL",
																"pubDate"},
        			null, null, null, null, null, null);
    		}
    		else {
    			
    			c = db.query(false, CHANNELS_TABLE, new String[] {"_id",
																"title",
																"author",
																"url",
																"imageURL",
																"pubDate"},
										null, null, null, null, null, null);
    		}

			if (c.moveToFirst()) {

				channels = new ArrayList<Channel>(c.getCount());
				
				do
				{
					channels.add(new Channel(c));
					
				} while (c.moveToNext());
			}

			c.close();

			db.execSQL("DROP TABLE channels");
			db.execSQL(CREATE_CHANNELS_TABLE);
			
			
			if (channels != null)
			{
				ContentValues values = new ContentValues();
				
				Log.d("PodcastDB", "channels: "+channels.size());
				
				for (Channel channel : channels)
				{
					values.put("_id", channel.id);
		            values.put("title", channel.title);
		        	values.put("author", channel.author);
		        	values.put("url", channel.url);
		        	values.put("imageURL", channel.imageURL);
		        	values.put("pubDate", channel.getPubDate());
		        	values.put("new_items", channel.new_items);
		            
		        	
		        	try
		        	{
		        		
		        		db.insertOrThrow(CHANNELS_TABLE, null, values);
		        	}
		        	catch (SQLException e) {
		        		
		        		db.update(CHANNELS_TABLE, values, "_id="+channel.id, null);
		        	}
		            
		            values.clear();
				}
			}
    	}
    	
    	if (oldVersion < 6)
    	{
    		Cursor c = db.query(false, ITEMS_TABLE, new String[] {"guid",
												                    "channel_id",
												                    "title",
												                    "pubDate",
												                    "listened",
												                    "position",
												                    "size",
												                    "duration",
												                    "enclosureURL",
												                    "description"},
								null, null, null, null, null, null);
    		
    		ArrayList<Item> items = new ArrayList<Item>();
    		
    		if (c.moveToFirst())
    		{
    			do
    			{
    				items.add(new Item(c));
    				
    			} while (c.moveToNext());
    		}
    		
    		c.close();
    		
    		db.execSQL("DROP TABLE items");
			db.execSQL(CREATE_ITEMS_TABLE);
			
			if (items != null)
			{
				ContentValues values = new ContentValues();
				
				for (Item item : items)
				{
					values.put("guid", item.guid);
					values.put("channel_id", item.channel_id);
					values.put("title", item.title);
					values.put("pubDate", item.getPubDate());
					values.put("listened", item.listened ? 1 : 0);
					values.put("size", item.size);
					values.put("duration", item.getDurationString());
					values.put("enclosureURL", item.enclosureURL);
					values.put("description", item.getDescription());
					
					db.insert(ITEMS_TABLE, null, values);
					values.clear();
				}
			}
    	}
    	
	}

    
    
    public PodcastDB(Context context)
    {
    	super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    
    public static void deleteChannel(Context context, int id)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	
        
        
        // delete any podcast items left behind by this channel
        Cursor c = db.query(false, ITEMS_TABLE, new String[] {"guid", "title"},
                "channel_id="+id, null, null, null, "pubDate DESC", null);
        
        c.moveToFirst();
        
        while (c.moveToNext())
        {
        	String guid = c.getString(0);
    		String filename = Util.getPodcastFilename(guid);
    		File file = new File(filename);
    		boolean ret = file.delete();
    		Log.d("PodcastDB", "deleteChannel - deleting: \""+filename+"\" ret: "+ret);
        }
        
        c.close();
        helper.close();
        
        // delete the podcast channel icon
        String iconFilename = Util.getIconFilename(PodcastDB.getImageURL(context, id));
        File iconFile = new File(iconFilename);
        iconFile.delete();
        
        // delete the database entries
        db.delete(CHANNELS_TABLE, "_id="+id, null);
        db.delete(ITEMS_TABLE, "channel_id="+id, null);
    }

    
    
    public static String getImageURL(Context context, int channel_id)
    {
    	String url = null;
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	Cursor c = db.query(false, CHANNELS_TABLE, new String[] {"imageURL"},
                			"_id == "+channel_id, null, null, null, null, null);
    	
    	if (c.moveToFirst()) {
    		
    		url = c.getString(0);
    	}
    	
    	c.close();
    	helper.close();
    	
    	return url;
    }
    
    
    public static String getChannelTitle(Context context, int channel_id)
    {
    	String title = null;
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	Cursor c = db.query(false, CHANNELS_TABLE, new String[] {"title"},
                			"_id == "+channel_id, null, null, null, null, null);
    	
    	if (c.moveToFirst()) {
    		
    		title = c.getString(0);
    	}
    	
    	c.close();
    	helper.close();
    	
    	return title;
    }
    
    
    
    
    public static int addRefresh(Context context, String url)
    {    	
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	int newItems = 0;
        int channel_id = -1;
        
        // Does this url already exist in the channels table?
        try
        {
            Cursor c = db.query(true, CHANNELS_TABLE, new String[] {"_id"},
                    "url='"+url+"'", null, null, null, null, "1");

            if (c.moveToFirst()) {
            
                channel_id = c.getInt(0);
            }
            
            c.close();

            URL channelURL = new URL(url);

            Log.d("PodcastDB", "addRefresh - connecting to "+url);
            
            HttpURLConnection conn = (HttpURLConnection) channelURL.openConnection();
            conn.setConnectTimeout(5000);
            conn.setReadTimeout(5000);
            
            InputStream stream = conn.getInputStream();
            
            Log.d("PodcastDB", "addRefresh - connected to "+url);
            
            SAXChannelHandler handler = new SAXChannelHandler(url);
            SAXParser parser = SAXParserFactory.newInstance().newSAXParser();
            parser.parse(stream, handler);

            conn.disconnect();

            Log.d("PodcastDB", "addRefresh - disconnected from "+url);
            
            Channel channel = handler.channel;
            channel.id = channel_id;
            ArrayList<Item> items = handler.items;

            // Add channel to channels table if it doesn't exist
            ContentValues values = new ContentValues();
            
            values.put("title", channel.title);
        	values.put("author", channel.author);
        	values.put("url", channel.url);
        	values.put("imageURL", channel.imageURL);
        	values.put("pubDate", channel.getPubDate());
            
            if (channel.id == -1) {
            	
            	values.put("new_items", items.size());
            	channel.id = (int) db.insert(CHANNELS_TABLE, null, values);
            }
            
            values.clear();
            
            
            
            // Add items to items table
            for (Item item : items)
            {
            	item.channel_id = channel.id;
                
                values.put("guid", item.guid);
                values.put("channel_id", item.channel_id);
                values.put("title", item.title);
                values.put("pubDate", item.getPubDate());
                values.put("listened", (item.listened ? 1 : 0));
                values.put("position", item.position);
                values.put("size", item.size);
                values.put("duration", item.getDurationString());
                values.put("enclosureURL", item.enclosureURL);
                values.put("description", item.getDescription());
                
                try
                {
                	db.insertOrThrow(ITEMS_TABLE, null, values);
                	
                	// If we didn't throw an exception, the item must be new
                	newItems++;
                }
                catch (SQLException e) {
                	/* Row already exists.  Update it instead without overwriting
                	 * attributes that have since been modified */
                	values.remove("listened");
                	values.remove("position");
                	values.remove("size");
                	values.remove("duration");
                	
                	db.update(ITEMS_TABLE, values, "guid = '"+item.guid+"'", null);                	
                }
                
                values.clear();
            }
            
            // update new_items in channels table
            c = db.query(false, ITEMS_TABLE, new String[] {"guid"},
                    "channel_id="+channel.id+" AND listened=0", null, null, null, null, null);
            
            channel.new_items = c.getCount();
            
            c.close();
            values.put("new_items", channel.new_items);
            db.update(CHANNELS_TABLE, values, "_id="+channel.id, null);

        }
        catch (MalformedURLException e) {
            Log.e("PodcastDB", "Exception", e);
        }
        catch (IOException e) {
        	Log.e("PodcastDB", "Exception", e);
        }
        catch (ParserConfigurationException e) {
        	Log.e("PodcastDB", "Exception", e);
        }
        catch (SAXException e) {
        	Log.e("PodcastDB", "Exception", e);
        }

        helper.close();
        
        Log.d("PodcastDB", "addRefresh(): "+newItems+" items");
        
        return newItems;
    }
    
    
    // begin playing most recent item by channel title
    //
    public static String query(Context context, String query)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	int channel_id = -1;
    	String guid = null;
    	
    	Cursor c = db.query(false, CHANNELS_TABLE, new String[] {"_id", "title"},
                null, null, null, null, null, null);
    	
    	if (c.moveToFirst())
    	{
    		do
    		{
    			int id = c.getInt(0);
    			String title = c.getString(1);
    			
    			if (title.equalsIgnoreCase(query)) {
    				channel_id = id;
    			}
    			
    		} while (channel_id < 0 && c.moveToNext());
    	}
    	
    	c.close();
    	
    	
    	if (channel_id > -1)
    	{
    		c = db.query(false, ITEMS_TABLE, new String[] {"guid"},
                    "channel_id="+channel_id+" AND listened = 0", null, null, null, "pubDate", "1");
        	
        	if (c.moveToFirst()) {
        		guid = c.getString(0);
        	}
        	
        	c.close();
    	}
    	
    	
    	helper.close();
    	
    	return guid;
    }
    
    
    
    public static ArrayList<String> getNewItems(Context context)
    {
    	ArrayList<String> newItems = new ArrayList<String>();
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	
    	Cursor c = db.query(false, ITEMS_TABLE, new String[] {"guid"},
                "listened = 0", null, null, null, "channel_id, pubDate", null);
    	
    	if (c.moveToFirst())
    	{
    		do
    		{
    			String guid = c.getString(0);
    			
    			if (Util.isDownloaded(guid) == false) {
    			
    				newItems.add(guid);
    			}
    			
    		} while (c.moveToNext());
    	}
    	
    	c.close();
    	helper.close();
    	
    	return newItems;
    }
    
    
    /**
     * Deletes old audio files stored on the SDCard.  This method will keep numItemsToKeep files
     * (derrived from the KEEP_OLD_ITEMS_PREF preference) while deleting the rest.  Additionally,
     * this method ensures that no more than 20 old items are stored in the database. 
     * 
     * @param context
     * @param channel_id
     * @param numItemsToKeepString
     */
    public static void deleteOldItems(Context context, int channel_id, String numItemsToKeepString)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	//String deleteWhere = null;
    	Cursor c = db.query(false, ITEMS_TABLE, new String[] {"guid", "title"},
                "channel_id="+channel_id+" AND listened = 1", null, null, null, "pubDate DESC", null);
    	
    	int size = c.getCount();
    	int numItemsToKeep = size;
    	
    	if (numItemsToKeepString.equals("ALL") == false) {
    		numItemsToKeep = Integer.parseInt(numItemsToKeepString);
    	}
    	
    	for (int i=numItemsToKeep; i < size; i++)
    	{
    		if (c.moveToPosition(i))
    		{
    			String guid = c.getString(0);
	    		String filename = Util.getPodcastFilename(guid);
	    		File file = new File(filename);
	    		file.delete();
	    		
	    		/*
	    		if (i >= 30) {
	    			
	    			if (deleteWhere == null) {
	    				deleteWhere = "guid='"+guid+"'";
	    			}
	    			else {
	    				deleteWhere += " OR guid='"+guid+"'";
	    			}
	    		}
	    		*/
	    		    		
	    		Log.d("PodcastDB", "deleting: "+c.getString(1));
    		}
    	}
    	
    	//Log.d("PodcastDB", "deleteWhere: "+deleteWhere);	
    	
    	c.close();
    	
    	/*
    	if (deleteWhere != null) {
    		db.delete(ITEMS_TABLE, deleteWhere, null);
    	}
    	*/
    	
    	helper.close();
    }
    
    
    
    
    public static int setItemListened(Context context, String item_guid, int channel_id, boolean listened)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	ContentValues values = new ContentValues();
    	int new_items = 0;
    	
    	values.put("listened", (listened ? 1 : 0));
    	
    	db.update(ITEMS_TABLE, values, "guid='"+item_guid+"'", null);
    	
    	values.clear();
    	
    	Cursor c = db.query(false, CHANNELS_TABLE, new String[] {"new_items"},
                "_id="+channel_id, null, null, null, null, null);
    	
    	if (c.moveToFirst()) {
    		
    		new_items = listened ? c.getInt(0) - 1 : c.getInt(0) + 1;
    		values.put("new_items", new_items);
    	}
    	
    	c.close();
    	
    	db.update(CHANNELS_TABLE, values, "_id="+channel_id, null);
    	
    	db.close();
    	helper.close();
    	
    	return new_items;
    }
    
    
    
    public static void setItemSize(Context context, String item_guid, int size)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	ContentValues values = new ContentValues();
    	
    	values.put("size", size);
    	
    	db.update(ITEMS_TABLE, values, "guid='"+item_guid+"'", null);
    	
    	db.close();
    	helper.close();
    }
    
    
    public static void setItemDuration(Context context, String item_guid, String duration)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	ContentValues values = new ContentValues();
    	
    	values.put("duration", duration);
    	
    	db.update(ITEMS_TABLE, values, "guid='"+item_guid+"'", null);
    	
    	db.close();
    	helper.close();
    }
    
    
    
    public static Item getItem(Context context, String guid, String[] columns)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	Item item = null;
    	
    	Cursor c = db.query(false, ITEMS_TABLE, columns,
                "guid=\""+guid+"\"", null, null, null, null, null);
    	
    	if (c.moveToFirst())
    	{
    		item = new Item(c);
    	}
    	
    	c.close();
    	helper.close();
    	
    	return item;
    }
    
    
    
    public static void getItem(Context context, Item item)
    {
    	if (item == null) {
    		return;
    	}
    	
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	Cursor c = db.query(false, ITEMS_TABLE, ITEMS_COLUMNS,
                "guid='"+item.guid+"'", null, null, null, null, null);
    	
    	if (c.moveToFirst())
    	{
    		item.readFromCursor(c);
    	}
    	
    	c.close();
    	helper.close();
    }
    
    
    
    public static boolean isListened(Context context, String item_guid)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	int listened = 0;
    	
    	Cursor c = db.query(false, ITEMS_TABLE, new String[] {"listened"},
                "guid='"+item_guid+"'", null, null, null, null, null);
    	
    	if (c.moveToFirst())
    	{
    		listened = c.getInt(0);
    	}
    	
    	c.close();
    	helper.close();
    	
    	return listened == 1;
    }
    
    
    
    public static int exportOPML(Context context)
    {
    	PodcastDB helper = new PodcastDB(context);
    	SQLiteDatabase db = helper.getReadableDatabase();
    	Cursor c = db.query(false, CHANNELS_TABLE, new String[] {"title", "url"},
                			null, null, null, null, null, null);
    	int nExported=0;
    	
    	if (c.moveToFirst())
    	{
    		long seconds = System.currentTimeMillis()/1000;
    		String filename = Util.getCacheDir() + "/export_" + seconds + ".opml";
    		FileOutputStream out = null;
    		
        	try {
        		out = new FileOutputStream(new File(filename));
        		out.write(("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
        				+"<opml version=\"1.0\">\n"
        				+"  <head>\n"
        				+"    <title>Export from Podcasts " + seconds + "</title>\n"
        				+"  </head>\n"
        				+"  <body>\n").getBytes());
        		
        		do
	    		{
        			nExported++;
        			String title = c.getString(0);
        			String url = c.getString(1);
        			out.write(("<outline text=\""+title+"\" "
        					+"title=\""+title+"\" "
        					+"type=\"rss\" "
        					+"xmlUrl=\""+url+"\"/>\n").getBytes());
	    			
	    		} while (c.moveToNext());
        		
        		out.write(("  </body>\n"
        				  +"</opml>").getBytes());
        		out.close();
        	}
        	catch (IOException ioe) {
        		Log.e("PodcastDB", "exportOPML caught exception", ioe);
        	}
    	}
    	
    	c.close();
    	helper.close();
    	
    	return nExported;
    }

}
